{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using the V3IO Frames Library for High-Performance Data Access \n",
    "\n",
    "- [Overview](#frames-overview)\n",
    "- [Initialization](#frames-init)\n",
    "- [Working with NoSQL Tables (kv Backend)](#frames-kv)\n",
    "- [Working with Time-Series Databases (tsdb Backend)](#frames-tsdb)\n",
    "- [Cleanup](#frames-cleanup)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-overview\"></a>\n",
    "## Overview\n",
    "\n",
    "[V3IO Frames](https://github.com/v3io/frames) (**\"Frames\"**) is a multi-model open-source data-access library, developed by Iguazio, which provides a unified high-performance DataFrame API for working with data in the data store of the Iguazio Data Science Platform (**\"the platform\"**).\n",
    "Frames currently supports the NoSQL (key-value) and time-series (TSDB) data models via its `nosql`|`kv` and `tsdb` backends.\n",
    "> **Note:** You can replace any reference to the `kv` backend in this tutorial with the `nosql` alias.\n",
    "\n",
    "To use Frames, you first need to import the **v3io_frames** library and create and initialize a client object &mdash; an instance of the`Client` class.<br>\n",
    "The `Client` class features the following object methods for supporting basic data operations; the type of data is derived from the backend type (`kv` &mdash; NoSQL table / `tsdb` &mdash; TSDB table):\n",
    "\n",
    "- `create` &mdash; creates a new TSDB table (\"backend data\").\n",
    "- `delete` &mdash; deletes a table.\n",
    "- `read` &mdash; reads data from a table into pandas DataFrames.\n",
    "- `write` &mdash; writes data from pandas DataFrames to a table.\n",
    "- `execute` &mdash; executes a command on a table.\n",
    "  Each backend may support multiple commands.\n",
    "\n",
    "For a detailed description of the Frames API, see the [Frames API reference](https://www.iguazio.com/docs/latest-release/reference/api-reference/frames/).<br>\n",
    "For more help and usage details, use the internal API help &mdash; `<client object>.<command>?` in Jupyter Notebook or `print(<client object>.<command>.__doc__)`.<br>\n",
    "For example, the following command returns information about the read operation for a client object named `client`:\n",
    "```\n",
    "client.read?\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-init\"></a>\n",
    "## Initialization\n",
    "\n",
    "To use V3IO Frames, first ensure that your platform tenant has a shared tenant-wide instance of the V3IO Frames service.\n",
    "This can be done by a platform service administrator from the **Services** dashboard page.<br>\n",
    "Then, import the required libraries and create a Frames client object (an instance of the `Client` class), as demonstrated in the following code, which creates a client object named `client`.\n",
    "\n",
    "> **Note:**\n",
    "> - The client constructor's `container` parameter is set to `\"users\"` for accessing data in the platform's \"users\" data container.\n",
    "> - Because no authentication credentials are passed to the constructor, Frames will use the access key that's assigned to the `V3IO_ACCESS_KEY` environment variable.\n",
    ">   The platform's Jupyter Notebook service defines this variable automatically and initializes it to a valid access key for the running user of the service.\n",
    ">   You can pass different credentials by using the constructor's `token` parameter (platform access key) or `user` and `password` parameters (platform username and password)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import v3io_frames as v3f\n",
    "import os\n",
    "\n",
    "# Create a Frames client\n",
    "client = v3f.Client(\"framesd:8081\", container=\"users\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='frames-kv'></a>\n",
    "## Working with NoSQL Tables (kv Backend)\n",
    "\n",
    "This section demonstrates how to use the `kv` Frames backend to write and read NoSQL data in the platform.\n",
    "\n",
    "- [Initialization](#frames-kv-init)\n",
    "- [Write to a NoSQL Table](#frames-kv-write)\n",
    "- [Read from the Table Using an SQL Query](#frames-kv-read-sql-query)\n",
    "- [Read from the Table Using the Frames API](#frames-kv-read-frames-api)\n",
    "  - [Read Using a Single DataFrame](#frames-kv-read-frames-api-single-df)\n",
    "  - [Read Using a DataFrames Iterator (Streaming)](#frames-kv-read-frames-api-df-iterator)\n",
    "- [Delete the NoSQL Table](#frames-kv-delete)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-kv-init\"></a>\n",
    "### Initialization\n",
    "\n",
    "Start out by defining table-path variables that will be used in the tutorial's code examples.<br>\n",
    "The table path (`table`) is relative to the configured parent data container; see [Write to a NoSQL Table](#frames-kv-write)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Relative path to the NoSQL table within the parent platform data container\n",
    "table = os.path.join(os.getenv(\"V3IO_USERNAME\"), \"examples\", \"bank\")\n",
    "\n",
    "# Full path to the NoSQL table for SQL queries (platform Presto data-path syntax);\n",
    "# use the same data container as used for the Frames client (\"users\")\n",
    "sql_table_path = 'v3io.users.\"' + table + '\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-kv-write\"></a>\n",
    "### Write to a NoSQL Table\n",
    "\n",
    "Read a file from a Simple Storage (S3) bucket into a Frames pandas DataFrame, and use the `write` method of the Frames client with the `kv` backend to write the data to a NoSQL table.<br>\n",
    "The mandatory `table` parameter specifies the relative table path within the data container that was configured for the Frames client (see the [main initialization](#frames-init) step).\n",
    "In the following example, the relative table path is set by using the `table` variable that was defined in the [kv backend initialization](#frames-kv-init) step.<br>\n",
    "The `dfs` parameter can be set either to a single DataFrame (as done in the following example) or to multiple DataFrames &mdash; either as a DataFrames iterator or as a list of DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the ingestion data by reading an AWS S3 file into a DataFrame\n",
    "df = pd.read_csv(\"https://s3.wasabisys.com/iguazio/data/bank/bank.csv\", sep=\";\")\n",
    "# Display DataFrame info & head (optional - for testing)\n",
    "# display(df.info(), df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write data from a DataFrame to a NoSQL table\n",
    "client.write(\"kv\", table=table, dfs=df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-kv-read-sql-query\"></a>\n",
    "### Read from the Table Using an SQL Query\n",
    "\n",
    "You can run SQL queries on your NoSQL table (using Presto) to offload data filtering, grouping, joins, etc. to a scale-out high-speed database engine.\n",
    "\n",
    "> **Note:** To query a table in a platform data container, the table path in the `from` section of the SQL query should be of the format `v3io.<container name>.\"/path/to/table\"`.\n",
    "> See [Presto Data Paths](https://www.iguazio.com/docs/latest-release/tutorials/getting-started/fundamentals/#data-paths-presto) in the platform documentation.\n",
    "> In the following example, the path is set by using the `sql_table_path` variable that was defined in the [kv backend initialization](#frames-kv-init) step.\n",
    "> Unless you changed the code, this variable translates to `v3io.users.\"<running user>/examples/bank\"`; for example, `v3io.users.\"iguazio/examples/bank\"` for user \"iguazio\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>loan</th>\n",
       "        <th>education</th>\n",
       "        <th>previous</th>\n",
       "        <th>housing</th>\n",
       "        <th>poutcome</th>\n",
       "        <th>duration</th>\n",
       "        <th>marital</th>\n",
       "        <th>default</th>\n",
       "        <th>balance</th>\n",
       "        <th>month</th>\n",
       "        <th>contact</th>\n",
       "        <th>campaign</th>\n",
       "        <th>y</th>\n",
       "        <th>idx</th>\n",
       "        <th>job</th>\n",
       "        <th>day</th>\n",
       "        <th>age</th>\n",
       "        <th>pdays</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>no</td>\n",
       "        <td>unknown</td>\n",
       "        <td>0</td>\n",
       "        <td>yes</td>\n",
       "        <td>unknown</td>\n",
       "        <td>117</td>\n",
       "        <td>divorced</td>\n",
       "        <td>no</td>\n",
       "        <td>10287</td>\n",
       "        <td>may</td>\n",
       "        <td>unknown</td>\n",
       "        <td>1</td>\n",
       "        <td>no</td>\n",
       "        <td>899</td>\n",
       "        <td>blue-collar</td>\n",
       "        <td>29</td>\n",
       "        <td>51</td>\n",
       "        <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>yes</td>\n",
       "        <td>tertiary</td>\n",
       "        <td>0</td>\n",
       "        <td>yes</td>\n",
       "        <td>unknown</td>\n",
       "        <td>197</td>\n",
       "        <td>divorced</td>\n",
       "        <td>no</td>\n",
       "        <td>13204</td>\n",
       "        <td>nov</td>\n",
       "        <td>cellular</td>\n",
       "        <td>2</td>\n",
       "        <td>no</td>\n",
       "        <td>3329</td>\n",
       "        <td>management</td>\n",
       "        <td>20</td>\n",
       "        <td>34</td>\n",
       "        <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>no</td>\n",
       "        <td>secondary</td>\n",
       "        <td>0</td>\n",
       "        <td>yes</td>\n",
       "        <td>unknown</td>\n",
       "        <td>163</td>\n",
       "        <td>married</td>\n",
       "        <td>no</td>\n",
       "        <td>10888</td>\n",
       "        <td>aug</td>\n",
       "        <td>cellular</td>\n",
       "        <td>1</td>\n",
       "        <td>no</td>\n",
       "        <td>4346</td>\n",
       "        <td>technician</td>\n",
       "        <td>5</td>\n",
       "        <td>44</td>\n",
       "        <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>no</td>\n",
       "        <td>secondary</td>\n",
       "        <td>0</td>\n",
       "        <td>no</td>\n",
       "        <td>unknown</td>\n",
       "        <td>5</td>\n",
       "        <td>married</td>\n",
       "        <td>no</td>\n",
       "        <td>10655</td>\n",
       "        <td>jul</td>\n",
       "        <td>telephone</td>\n",
       "        <td>3</td>\n",
       "        <td>no</td>\n",
       "        <td>3967</td>\n",
       "        <td>technician</td>\n",
       "        <td>31</td>\n",
       "        <td>48</td>\n",
       "        <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>yes</td>\n",
       "        <td>secondary</td>\n",
       "        <td>0</td>\n",
       "        <td>no</td>\n",
       "        <td>unknown</td>\n",
       "        <td>77</td>\n",
       "        <td>married</td>\n",
       "        <td>no</td>\n",
       "        <td>13229</td>\n",
       "        <td>jul</td>\n",
       "        <td>telephone</td>\n",
       "        <td>2</td>\n",
       "        <td>no</td>\n",
       "        <td>3213</td>\n",
       "        <td>admin.</td>\n",
       "        <td>8</td>\n",
       "        <td>49</td>\n",
       "        <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>no</td>\n",
       "        <td>tertiary</td>\n",
       "        <td>3</td>\n",
       "        <td>yes</td>\n",
       "        <td>success</td>\n",
       "        <td>638</td>\n",
       "        <td>single</td>\n",
       "        <td>no</td>\n",
       "        <td>13711</td>\n",
       "        <td>may</td>\n",
       "        <td>cellular</td>\n",
       "        <td>1</td>\n",
       "        <td>no</td>\n",
       "        <td>1779</td>\n",
       "        <td>technician</td>\n",
       "        <td>14</td>\n",
       "        <td>32</td>\n",
       "        <td>175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>no</td>\n",
       "        <td>primary</td>\n",
       "        <td>4</td>\n",
       "        <td>yes</td>\n",
       "        <td>success</td>\n",
       "        <td>146</td>\n",
       "        <td>married</td>\n",
       "        <td>no</td>\n",
       "        <td>12519</td>\n",
       "        <td>apr</td>\n",
       "        <td>cellular</td>\n",
       "        <td>2</td>\n",
       "        <td>no</td>\n",
       "        <td>602</td>\n",
       "        <td>blue-collar</td>\n",
       "        <td>17</td>\n",
       "        <td>50</td>\n",
       "        <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>no</td>\n",
       "        <td>secondary</td>\n",
       "        <td>0</td>\n",
       "        <td>yes</td>\n",
       "        <td>unknown</td>\n",
       "        <td>135</td>\n",
       "        <td>divorced</td>\n",
       "        <td>no</td>\n",
       "        <td>10787</td>\n",
       "        <td>jun</td>\n",
       "        <td>unknown</td>\n",
       "        <td>2</td>\n",
       "        <td>no</td>\n",
       "        <td>3345</td>\n",
       "        <td>technician</td>\n",
       "        <td>4</td>\n",
       "        <td>31</td>\n",
       "        <td>-1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('no', 'unknown', 0, 'yes', 'unknown', 117, 'divorced', 'no', 10287, 'may', 'unknown', 1, 'no', 899, 'blue-collar', 29, 51, -1),\n",
       " ('yes', 'tertiary', 0, 'yes', 'unknown', 197, 'divorced', 'no', 13204, 'nov', 'cellular', 2, 'no', 3329, 'management', 20, 34, -1),\n",
       " ('no', 'secondary', 0, 'yes', 'unknown', 163, 'married', 'no', 10888, 'aug', 'cellular', 1, 'no', 4346, 'technician', 5, 44, -1),\n",
       " ('no', 'secondary', 0, 'no', 'unknown', 5, 'married', 'no', 10655, 'jul', 'telephone', 3, 'no', 3967, 'technician', 31, 48, -1),\n",
       " ('yes', 'secondary', 0, 'no', 'unknown', 77, 'married', 'no', 13229, 'jul', 'telephone', 2, 'no', 3213, 'admin.', 8, 49, -1),\n",
       " ('no', 'tertiary', 3, 'yes', 'success', 638, 'single', 'no', 13711, 'may', 'cellular', 1, 'no', 1779, 'technician', 14, 32, 175),\n",
       " ('no', 'primary', 4, 'yes', 'success', 146, 'married', 'no', 12519, 'apr', 'cellular', 2, 'no', 602, 'blue-collar', 17, 50, 147),\n",
       " ('no', 'secondary', 0, 'yes', 'unknown', 135, 'divorced', 'no', 10787, 'jun', 'unknown', 2, 'no', 3345, 'technician', 4, 31, -1)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from $sql_table_path where balance > 10000 limit 8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-kv-read-frames-api\"></a>\n",
    "### Read from the Table Using the Frames API\n",
    "\n",
    "Use the `read` method of the Frames client with the `kv` backend to read data from your NoSQL table.<br>\n",
    "The `read` method can return a DataFrame or a DataFrames iterator (a stream), as demonstrated in the following examples.\n",
    "\n",
    "- [Read Using a Single DataFrame](#frames-kv-read-frames-api-single-df)\n",
    "- [Read Using a DataFrames Iterator (Streaming)](#frames-kv-read-frames-api-df-iterator)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-kv-read-frames-api-single-df\"></a>\n",
    "#### Read Using a Single DataFrame\n",
    "\n",
    "The following example uses a single command to read data from the NoSQL table into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>balance</th>\n",
       "      <th>campaign</th>\n",
       "      <th>contact</th>\n",
       "      <th>day</th>\n",
       "      <th>default</th>\n",
       "      <th>duration</th>\n",
       "      <th>education</th>\n",
       "      <th>housing</th>\n",
       "      <th>job</th>\n",
       "      <th>loan</th>\n",
       "      <th>marital</th>\n",
       "      <th>month</th>\n",
       "      <th>pdays</th>\n",
       "      <th>poutcome</th>\n",
       "      <th>previous</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>idx</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3700</th>\n",
       "      <td>60</td>\n",
       "      <td>71188</td>\n",
       "      <td>1</td>\n",
       "      <td>cellular</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "      <td>205</td>\n",
       "      <td>primary</td>\n",
       "      <td>no</td>\n",
       "      <td>retired</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>oct</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3332</th>\n",
       "      <td>31</td>\n",
       "      <td>22546</td>\n",
       "      <td>6</td>\n",
       "      <td>cellular</td>\n",
       "      <td>14</td>\n",
       "      <td>no</td>\n",
       "      <td>8</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>yes</td>\n",
       "      <td>management</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>267</td>\n",
       "      <td>failure</td>\n",
       "      <td>4</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2624</th>\n",
       "      <td>53</td>\n",
       "      <td>22370</td>\n",
       "      <td>1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>15</td>\n",
       "      <td>no</td>\n",
       "      <td>106</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>yes</td>\n",
       "      <td>entrepreneur</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2776</th>\n",
       "      <td>37</td>\n",
       "      <td>22856</td>\n",
       "      <td>1</td>\n",
       "      <td>cellular</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "      <td>154</td>\n",
       "      <td>primary</td>\n",
       "      <td>no</td>\n",
       "      <td>management</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>jul</td>\n",
       "      <td>388</td>\n",
       "      <td>failure</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1483</th>\n",
       "      <td>43</td>\n",
       "      <td>27733</td>\n",
       "      <td>7</td>\n",
       "      <td>unknown</td>\n",
       "      <td>3</td>\n",
       "      <td>no</td>\n",
       "      <td>164</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>yes</td>\n",
       "      <td>technician</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>jun</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>33</td>\n",
       "      <td>23663</td>\n",
       "      <td>2</td>\n",
       "      <td>cellular</td>\n",
       "      <td>16</td>\n",
       "      <td>no</td>\n",
       "      <td>199</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>yes</td>\n",
       "      <td>housemaid</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>apr</td>\n",
       "      <td>146</td>\n",
       "      <td>failure</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4334</th>\n",
       "      <td>37</td>\n",
       "      <td>20453</td>\n",
       "      <td>1</td>\n",
       "      <td>telephone</td>\n",
       "      <td>4</td>\n",
       "      <td>no</td>\n",
       "      <td>115</td>\n",
       "      <td>secondary</td>\n",
       "      <td>yes</td>\n",
       "      <td>entrepreneur</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>may</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1031</th>\n",
       "      <td>49</td>\n",
       "      <td>25824</td>\n",
       "      <td>1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>17</td>\n",
       "      <td>no</td>\n",
       "      <td>94</td>\n",
       "      <td>primary</td>\n",
       "      <td>no</td>\n",
       "      <td>retired</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>jun</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      age  balance  campaign    contact  day default  duration  education  \\\n",
       "idx                                                                         \n",
       "3700   60    71188         1   cellular    6      no       205    primary   \n",
       "3332   31    22546         6   cellular   14      no         8   tertiary   \n",
       "2624   53    22370         1    unknown   15      no       106   tertiary   \n",
       "2776   37    22856         1   cellular    2      no       154    primary   \n",
       "1483   43    27733         7    unknown    3      no       164   tertiary   \n",
       "650    33    23663         2   cellular   16      no       199   tertiary   \n",
       "4334   37    20453         1  telephone    4      no       115  secondary   \n",
       "1031   49    25824         1    unknown   17      no        94    primary   \n",
       "\n",
       "     housing           job loan  marital month  pdays poutcome  previous   y  \n",
       "idx                                                                           \n",
       "3700      no       retired   no  married   oct     -1  unknown         0  no  \n",
       "3332     yes    management   no  married   may    267  failure         4  no  \n",
       "2624     yes  entrepreneur   no  married   may     -1  unknown         0  no  \n",
       "2776      no    management   no  married   jul    388  failure         1  no  \n",
       "1483     yes    technician   no   single   jun     -1  unknown         0  no  \n",
       "650      yes     housemaid   no   single   apr    146  failure         2  no  \n",
       "4334     yes  entrepreneur   no   single   may     -1  unknown         0  no  \n",
       "1031      no       retired   no   single   jun     -1  unknown         0  no  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = client.read(backend=\"kv\", table=table, filter=\"balance > 20000\")\n",
    "df.head(8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-kv-read-frames-api-df-iterator\"></a>\n",
    "#### Read Using a DataFrames Iterator (Streaming)\n",
    "\n",
    "The following example uses a DataFrames iterator to stream data from the NoSQL table into multiple DataFrames and allow concurrent data movement and processing.<br>\n",
    "The example sets the `iterator` parameter to `True` to receive a DataFrames iterator (instead of the default single DataFrame), and then iterates the DataFrames in the returned iterator; you can also use `concat` instead of iterating the DataFrames.\n",
    "\n",
    "> **Note:** Iterators work with all Frames backends and can be used as input to write functions that support this, such as the `write` method of the Frames client."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>balance</th>\n",
       "      <th>campaign</th>\n",
       "      <th>contact</th>\n",
       "      <th>day</th>\n",
       "      <th>default</th>\n",
       "      <th>duration</th>\n",
       "      <th>education</th>\n",
       "      <th>housing</th>\n",
       "      <th>job</th>\n",
       "      <th>loan</th>\n",
       "      <th>marital</th>\n",
       "      <th>month</th>\n",
       "      <th>pdays</th>\n",
       "      <th>poutcome</th>\n",
       "      <th>previous</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>idx</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3700</th>\n",
       "      <td>60</td>\n",
       "      <td>71188</td>\n",
       "      <td>1</td>\n",
       "      <td>cellular</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "      <td>205</td>\n",
       "      <td>primary</td>\n",
       "      <td>no</td>\n",
       "      <td>retired</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>oct</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3332</th>\n",
       "      <td>31</td>\n",
       "      <td>22546</td>\n",
       "      <td>6</td>\n",
       "      <td>cellular</td>\n",
       "      <td>14</td>\n",
       "      <td>no</td>\n",
       "      <td>8</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>yes</td>\n",
       "      <td>management</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>267</td>\n",
       "      <td>failure</td>\n",
       "      <td>4</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1483</th>\n",
       "      <td>43</td>\n",
       "      <td>27733</td>\n",
       "      <td>7</td>\n",
       "      <td>unknown</td>\n",
       "      <td>3</td>\n",
       "      <td>no</td>\n",
       "      <td>164</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>yes</td>\n",
       "      <td>technician</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>jun</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>33</td>\n",
       "      <td>23663</td>\n",
       "      <td>2</td>\n",
       "      <td>cellular</td>\n",
       "      <td>16</td>\n",
       "      <td>no</td>\n",
       "      <td>199</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>yes</td>\n",
       "      <td>housemaid</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>apr</td>\n",
       "      <td>146</td>\n",
       "      <td>failure</td>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4334</th>\n",
       "      <td>37</td>\n",
       "      <td>20453</td>\n",
       "      <td>1</td>\n",
       "      <td>telephone</td>\n",
       "      <td>4</td>\n",
       "      <td>no</td>\n",
       "      <td>115</td>\n",
       "      <td>secondary</td>\n",
       "      <td>yes</td>\n",
       "      <td>entrepreneur</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>may</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      age  balance  campaign    contact  day default  duration  education  \\\n",
       "idx                                                                         \n",
       "3700   60    71188         1   cellular    6      no       205    primary   \n",
       "3332   31    22546         6   cellular   14      no         8   tertiary   \n",
       "1483   43    27733         7    unknown    3      no       164   tertiary   \n",
       "650    33    23663         2   cellular   16      no       199   tertiary   \n",
       "4334   37    20453         1  telephone    4      no       115  secondary   \n",
       "\n",
       "     housing           job loan  marital month  pdays poutcome  previous   y  \n",
       "idx                                                                           \n",
       "3700      no       retired   no  married   oct     -1  unknown         0  no  \n",
       "3332     yes    management   no  married   may    267  failure         4  no  \n",
       "1483     yes    technician   no   single   jun     -1  unknown         0  no  \n",
       "650      yes     housemaid   no   single   apr    146  failure         2  no  \n",
       "4334     yes  entrepreneur   no   single   may     -1  unknown         0  no  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dfs = client.read(backend=\"kv\", table=table, filter=\"balance > 20000\",\n",
    "                  iterator=True)\n",
    "for df in dfs:\n",
    "    display(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-kv-delete\"></a>\n",
    "### Delete the NoSQL Table\n",
    "\n",
    "Use the `delete` method of the Frames client with the `kv` backend to delete the NoSQL table that was used in the previous steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete the `table` NoSQL table\n",
    "client.delete(\"kv\", table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='frames-tsdb'></a>\n",
    "## Working with Time-Series Databases (tsdb Backend)\n",
    "\n",
    "This section demonstrates how to use the `tsdb` Frames backend to create a time-series database (TSDB) table in the platform, ingest data into the table, and read from the table (i.e., submit TSDB queries).\n",
    "\n",
    "- [Initialization](#frames-tsdb-init)\n",
    "- [Create a TSDB Table](#frames-tsdb-create)\n",
    "- [Write to the TSDB Table](#frames-tsdb-write)\n",
    "- [Read from the TSDB Table](#frames-tsdb-read)\n",
    "  - [Conditional Read](#frames-tsdb-read-conditional)\n",
    "- [Delete the TSDB Table](#frames-tsdb-delete)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-tsdb-init\"></a>\n",
    "### Initialization\n",
    "\n",
    "Start out by defining a TSDB table-path variable that will be used in the tutorial's code examples.<br>\n",
    "The table path (`tsdb_table`) is relative to the configured parent data container; see [Create a TSDB Table](#frames-tsdb-create)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Relative path to the TSDB table within the parent platform data container\n",
    "tsdb_table = os.path.join(os.getenv(\"V3IO_USERNAME\"), \"examples\", \"tsdb_tab\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-tsdb-create\"></a>\n",
    "### Create a TSDB Table\n",
    "\n",
    "Use the `create` method of the Frames client with the `tsdb` backend to create a new TSDB table.<br>\n",
    "The mandatory `table` parameter specifies the relative table path within the data container that was configured for the Frames client (see the [main initialization](#frames-init) step).\n",
    "In the following example, the relative table path is set by using the `tsdb_table` variable that was defined in the [tsdb backend initialization](#frames-tsdb-init) step.<br>\n",
    "You must set the `rate` argument to the ingestion rate of the TSDB metric-samples, as `\"[0-9]+/[smh]\"` (where '`s`' = seconds, '`m`' = minutes, and '`h`' = hours); for example, `1/s` (one sample per minute).\n",
    "It's recommended that you set the rate to the average expected ingestion rate, and that the ingestion rates for a given TSDB table don't vary significantly; when there's a big difference in the ingestion rates (for example, x10), use separate TSDB tables.\n",
    "You can also set additional optional arguments, such as `aggregates` or `aggregation_granularity`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new TSDB table; ingestion rate = one sample per hour (\"1/h\")\n",
    "client.create(backend=\"tsdb\", table=tsdb_table, rate=\"1/h\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-tsdb-write\"></a>\n",
    "### Write to the TSDB Table\n",
    "\n",
    "Use the `write` method of the Frames client with the `tsdb` backend to ingest data from a pandas DataFrame into your TSDB table.<br>\n",
    "The primary-key attribute of platform TSDB tables (i.e., the DataFrame index column) must hold the sample time of the data (displayed as `time` in read outputs).<br>\n",
    "In addition, TSDB table items (rows) can optionally have sub-index columns (attributes) that are called labels.\n",
    "You can add labels to TSDB table items in one of two ways; you can also combine these methods:\n",
    "\n",
    "- Use the `labels` dictionary parameter of the `write` method to add labels to all the written metric-sample table items (DataFrame rows) &mdash; `{<label>: <value>[, <label>: <value>, ...]}`.<br>\n",
    "  For example, `{\"node\": \"11\", \"os\": \"linux\"}`.\n",
    "  Note that the label values must be provided as strings.\n",
    "- Define DataFrame index columns for the labels.\n",
    "  All DataFrame index columns except for the sample-time index column are automatically converted into labels for the respective table items.\n",
    "  > **Note:** If you wish to use regular columns in your DataFrames as metric labels, convert these columns to index columns.\n",
    "  > The following example converts the `symbol` and `exchange` columns to index columns that will be used as metric labels (in addition to the `time` index column):<br>\n",
    "  > ```python\n",
    "  > df.index.name=\"time\"                              # Name the sample-time index column \"time\"\n",
    "  > df.reset_index(level=0, inplace=True)             # Reset the DataFrame indexes\n",
    "  > df = df.set_index([\"time\", \"symbol\", \"exchange\"]) # Define the time and label columns as index columns\n",
    "  > ```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "\n",
    "# Generate a DataFrame with TSDB metric samples and a \"time\" index column\n",
    "def gen_df_w_tsdb_data(num_items=24, freq=\"1H\", end=None, start=None,\n",
    "                       start_delta=None, tz=None, normalize=False, zero=False,\n",
    "                       attrs=[\"cpu\", \"mem\", \"disk\"]):\n",
    "    if (start is None and start_delta is not None and end is not None):\n",
    "        start = end - timedelta(days=start_delta)\n",
    "    if (zero):\n",
    "        if (end is not None):\n",
    "            end = end.replace(minute=0, second=0, microsecond=0)\n",
    "        if (start is not None):\n",
    "            start = start.replace(minute=0, second=0, microsecond=0)\n",
    "    # If `start`, `end`, `num_items` (date_range() `periods`), and `freq`\n",
    "    # are set, ignore `freq`\n",
    "    if (freq is not None and start is not None and end is not None and\n",
    "            num_items is not None):\n",
    "        freq = None\n",
    "    times = pd.date_range(periods=num_items, freq=freq, start=start, end=end,\n",
    "                          tz=tz, normalize=normalize)\n",
    "    data = np.random.rand(num_items, len(attrs)) * 100\n",
    "    df = pd.DataFrame(data, index=times, columns=attrs)\n",
    "    df.index.name = \"time\"\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare DataFrames with randomly generated metric samples\n",
    "end_t = datetime.now()\n",
    "start_delta = 7  # start time = ent_t - 7 days\n",
    "dfs = []\n",
    "for i in range(4):\n",
    "    # Generate a new DataFrame with TSDB metrics\n",
    "    dfs.append(gen_df_w_tsdb_data(end=end_t, start_delta=7, zero=True))\n",
    "    # Display DataFrame info & head (optional - for testing)\n",
    "    # print(\"\\n** dfs[\" + str(i) + \"] **\")\n",
    "    # display(dfs[i].info(), dfs[i].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write to a TSDB table\n",
    "\n",
    "# Prepare metric labels to write\n",
    "labels = [\n",
    "    {\"node\": \"11\", \"os\": \"linux\"},\n",
    "    {\"node\": \"2\", \"os\": \"windows\"},\n",
    "    {\"node\": \"11\", \"os\": \"windows\"},\n",
    "    {\"node\": \"2\", \"os\": \"linux\"}\n",
    "]\n",
    "\n",
    "# Write the contents of the prepared DataFrames to a TSDB table. Use multiple\n",
    "# write commands with the `labels` parameter to set different label values.\n",
    "num_dfs = len(dfs)\n",
    "for i in range(num_dfs):\n",
    "    client.write(\"tsdb\", table=tsdb_table, dfs=dfs[i], labels=labels[i])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-tsdb-read\"></a>\n",
    "### Read from the TSDB Table\n",
    "\n",
    "- [Overview and Basic Examples](#frames-tsdb-read-basic)\n",
    "- [Conditional Read](#frames-tsdb-read-conditional)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-tsdb-read-basic\"></a>\n",
    "#### Overview and Basic Examples\n",
    "\n",
    "Use the `read` method of the Frames client with the `tsdb` backend to read data from your TSDB table (i.e., query the database).<br>\n",
    "Note that you cannot mix raw sample-data queries and aggregation queries.\n",
    "\n",
    "You must set the `table` parameter to the path to the TSDB table.<br>\n",
    "You can optionally set additional method parameters to configure the query:\n",
    "\n",
    "- `columns` defines the query metrics (default = all).\n",
    "- `aggregators` defines aggregation functions (\"aggregators\") to execute for all the configured metrics.\n",
    "- `filter` restricts the query by using a platform [filter expression](https://www.iguazio.com/docs/latest-release/reference/expressions/condition-expression/#filter-expression).\n",
    "- `start` and `end` define the query's time range &mdash; the metric-sample timestamps to which to apply the query.\n",
    "   The default `end` time is `\"now\"` and the default `start` time is 1 hour before the end time (`<end> - 1h`).\n",
    "- `step` defines the interval for aggregation or raw-data downsampling (default = the query's time range).\n",
    "- `multi_index` casn be set to `True` to return labels as index columns, as demonstrated in the following examples.\n",
    "  By default, only the metric sample-time primary-key attribute is returned as an index column.\n",
    "\n",
    "See the [Frames API reference](https://www.iguazio.com/docs/latest-release/reference/api-reference/frames/tsdb/read/) for more information about the `read` parameters that are supported for the `tsdb` backend."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>cpu</th>\n",
       "      <th>disk</th>\n",
       "      <th>mem</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>node</th>\n",
       "      <th>os</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-03-22 20:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>71.245621</td>\n",
       "      <td>91.954313</td>\n",
       "      <td>20.934462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-23 03:18:15.652000+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>3.868040</td>\n",
       "      <td>69.360057</td>\n",
       "      <td>23.388007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-23 10:36:31.304000+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>72.031874</td>\n",
       "      <td>85.322267</td>\n",
       "      <td>86.397208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-23 17:54:46.956000+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>34.625169</td>\n",
       "      <td>2.750782</td>\n",
       "      <td>51.012280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-24 01:13:02.608000+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>63.849504</td>\n",
       "      <td>53.506142</td>\n",
       "      <td>69.549750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-24 08:31:18.260000+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>28.244368</td>\n",
       "      <td>78.966694</td>\n",
       "      <td>40.742563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-24 15:49:33.913000+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>95.323246</td>\n",
       "      <td>47.470774</td>\n",
       "      <td>68.003333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-24 23:07:49.565000+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>86.179592</td>\n",
       "      <td>21.568282</td>\n",
       "      <td>54.339696</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   cpu       disk        mem\n",
       "time                             node os                                    \n",
       "2020-03-22 20:00:00+00:00        11   linux  71.245621  91.954313  20.934462\n",
       "2020-03-23 03:18:15.652000+00:00 11   linux   3.868040  69.360057  23.388007\n",
       "2020-03-23 10:36:31.304000+00:00 11   linux  72.031874  85.322267  86.397208\n",
       "2020-03-23 17:54:46.956000+00:00 11   linux  34.625169   2.750782  51.012280\n",
       "2020-03-24 01:13:02.608000+00:00 11   linux  63.849504  53.506142  69.549750\n",
       "2020-03-24 08:31:18.260000+00:00 11   linux  28.244368  78.966694  40.742563\n",
       "2020-03-24 15:49:33.913000+00:00 11   linux  95.323246  47.470774  68.003333\n",
       "2020-03-24 23:07:49.565000+00:00 11   linux  86.179592  21.568282  54.339696"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Read all metrics from the TSDB table (start=\"0\"; default `end` time = \"now\")\n",
    "# into a single DataFrame (default `Iterator`=False) and display the first 10\n",
    "# items; show metric labels as index columns (multi_index=True)\n",
    "df = client.read(backend=\"tsdb\", table=tsdb_table, start=\"0\", multi_index=True)\n",
    "display(df.head(8))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-tsdb-read-conditional\"></a>\n",
    "#### Conditional Read\n",
    "\n",
    "The following example demonstrates how to use a query filter to conditionally read only a subset of the data from a TSDB table.\n",
    "This is done by setting the value of the `filter` parameter to a [platform filter expression](https://www.iguazio.com/docs/latest-release/reference/expressions/condition-expression/#filter-expression)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count(cpu)</th>\n",
       "      <th>count(disk)</th>\n",
       "      <th>count(mem)</th>\n",
       "      <th>sum(cpu)</th>\n",
       "      <th>sum(disk)</th>\n",
       "      <th>sum(mem)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>node</th>\n",
       "      <th>os</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-03-22 00:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>71.245621</td>\n",
       "      <td>91.954313</td>\n",
       "      <td>20.934462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-23 00:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>110.525082</td>\n",
       "      <td>157.433106</td>\n",
       "      <td>160.797495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-24 00:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>273.596711</td>\n",
       "      <td>201.511892</td>\n",
       "      <td>232.635343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-25 00:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>183.166730</td>\n",
       "      <td>106.699769</td>\n",
       "      <td>167.292110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-26 00:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>71.736371</td>\n",
       "      <td>163.898363</td>\n",
       "      <td>171.576670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-27 00:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>166.073091</td>\n",
       "      <td>78.634711</td>\n",
       "      <td>186.968027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-28 00:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>134.209306</td>\n",
       "      <td>163.710346</td>\n",
       "      <td>127.641747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-29 00:00:00+00:00</th>\n",
       "      <th>11</th>\n",
       "      <th>linux</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>171.002976</td>\n",
       "      <td>256.766286</td>\n",
       "      <td>134.784722</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      count(cpu)  count(disk)  count(mem)  \\\n",
       "time                      node os                                           \n",
       "2020-03-22 00:00:00+00:00 11   linux         1.0          1.0         1.0   \n",
       "2020-03-23 00:00:00+00:00 11   linux         3.0          3.0         3.0   \n",
       "2020-03-24 00:00:00+00:00 11   linux         4.0          4.0         4.0   \n",
       "2020-03-25 00:00:00+00:00 11   linux         3.0          3.0         3.0   \n",
       "2020-03-26 00:00:00+00:00 11   linux         3.0          3.0         3.0   \n",
       "2020-03-27 00:00:00+00:00 11   linux         3.0          3.0         3.0   \n",
       "2020-03-28 00:00:00+00:00 11   linux         4.0          4.0         4.0   \n",
       "2020-03-29 00:00:00+00:00 11   linux         3.0          3.0         3.0   \n",
       "\n",
       "                                        sum(cpu)   sum(disk)    sum(mem)  \n",
       "time                      node os                                         \n",
       "2020-03-22 00:00:00+00:00 11   linux   71.245621   91.954313   20.934462  \n",
       "2020-03-23 00:00:00+00:00 11   linux  110.525082  157.433106  160.797495  \n",
       "2020-03-24 00:00:00+00:00 11   linux  273.596711  201.511892  232.635343  \n",
       "2020-03-25 00:00:00+00:00 11   linux  183.166730  106.699769  167.292110  \n",
       "2020-03-26 00:00:00+00:00 11   linux   71.736371  163.898363  171.576670  \n",
       "2020-03-27 00:00:00+00:00 11   linux  166.073091   78.634711  186.968027  \n",
       "2020-03-28 00:00:00+00:00 11   linux  134.209306  163.710346  127.641747  \n",
       "2020-03-29 00:00:00+00:00 11   linux  171.002976  256.766286  134.784722  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Read over-time aggregates with a 1-day aggregation step for all metric\n",
    "# samples in the table with the `os` label \"linux\" and the `node` label 11.\n",
    "df = client.read(backend=\"tsdb\", table=tsdb_table, aggregators=\"count,sum\",\n",
    "                 step=\"1d\", start=\"0\", filter=\"os=='linux' and node=='11'\",\n",
    "                 multi_index=True)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-tsdb-delete\"></a>\n",
    "### Delete the TSDB Table\n",
    "\n",
    "Use the `delete` method of the Frames client with the `tsdb` backend to delete the TSDB table that was used in the previous steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.delete(\"tsdb\", tsdb_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"frames-cleanup\"></a>\n",
    "## Cleanup\n",
    "\n",
    "You can optionally delete any of the directories or files that you created.\n",
    "See the instructions in the [Creating and Deleting Container Directories](https://www.iguazio.com/docs/latest-release/tutorials/getting-started/containers/#create-delete-container-dirs) tutorial.\n",
    "For example, the following code uses a local file-system command to delete the entire **&lt;running user&gt;/examples/** directory in the \"users\" container.\n",
    "Edit the path, as needed, then remove the comment mark (`#`) and run the code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!rm -rf /User/examples/"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
